#!/usr/bin/env python
# coding: utf-8 

__version__ = '0.1'
__author__ = 'YANG Lingyun'

import cx_Oracle
import padnums
import sys

def batch_query_print(query_requests):
    '''批量运行query_result，query_requests为列表，其的元素为
    {'db_conn_str':db_conn_str,'stat':stat, 'params':params}'''
    for query in query_requests:
        
        db_conn_str = query['db_conn_str']
        stat = query['stat']
        params = query['params']

        query_print(db_conn_str, stat, params)

def query_print(db_conn_str, stat, params):
    '''查询并打印某个SQL的结果'''
    table = query_result(db_conn_str, stat, params)
    table_name = get_table_name(stat)
    print table_name.upper()+':'
    padnums.pprint_table(sys.stdout, invert_table(table))

    print  #打印空行

def query_result(db_conn_str, stat, params):
    '''
    查询数据库，并以二维列表的形式返回。返回列表第一行为查询的结果列名。
    '''
    # 1. 取得sql语句中的列名，如果为*号的话，需要从数据库中查询得到列名，
    # 而且需要重新组装sql语句
    column_names = get_column_names(stat)
    if column_names[0][0] == '*':
        table_name = get_table_name(stat)
        column_names = query_column_names(db_conn_str, table_name)
        new_stat = reconstruct_stat(stat, column_names)
    else :
        new_stat = stat

    # 2. 查询数据库取得结果
    result_table = sql_run_template(db_conn_str, new_stat, params)
    
    # 3. 组装列名与查询结果
    result_table.insert(0, column_names)

    return result_table


def sql_run_template(db_conn_str, stat, params):
    '''
    sql运行模板，运行一个sql语句，并以二维列表的形式返回结果
    '''
    conn = cx_Oracle.connect(db_conn_str)
    cursor = conn.cursor()
    cursor.prepare(stat)
    cursor.execute(None, params)
    
    table = []
    for row in cursor:
        record = []
        for column in row:
            record.append(str(column))
        table.append(record)

    return table

def get_column_names(stat):
    '''
    解析sql语句得到列名集合
    '''
    # 截取 select 与 from 之间的部分
    begin = stat.find('select') + len('select')
    end = stat.find('from')
    column_names = []
    for column in stat[begin:end].split(','):
        column_names.append(column.strip().split(' ')[0])

    return column_names

def query_column_names(db_conn_str, table_name):
    '''
    从数据库中查询得到列名集合
    '''
    column_names = []
    
    table_name_items = table_name.split('.')

    if len(table_name_items) == 1:
        stat = """select column_name from user_tab_cols where table_name = :table_name"""
        result_table = sql_run_template(
            db_conn_str, 
            stat, 
            {'table_name':table_name.upper()}) # 在oracle内部表名是大写的
        for row in result_table:
            for column in row:
                column_names.append(column.lower()) # 转换为小写
    else:
        stat = """select column_name from all_tab_columns where owner = :scheme and table_name = :table_name"""
        scheme = table_name_items[0]
        table_name = table_name_items[1]
        result_table = sql_run_template(
            db_conn_str, 
            stat, 
            {'scheme':scheme.upper(),'table_name':table_name.upper()}) # 在oracle内部表名是大写的
        for row in result_table:
            for column in row:
                column_names.append(column.lower()) # 转换为小写

    return column_names

def get_table_name(stat):
    '''
    解析sql语句取得表名
    '''
    begin = stat.find('from') + len('from')
    end = stat.find('where')
    return stat[begin:end].split()[0]

def reconstruct_stat(stat, column_names):
    '''
    重新组装sql语句，把*号替换为实际的列名
    '''
    star = stat.find('*')
    column_str = ''
    for column_name in column_names:
        column_str = column_str + column_name + ','
    
    return stat[0:star] + column_str[:-1] + stat[star+len('*'):]

def invert_table(table):
    '''反转表格，即交换表格的行与列'''
    inverted_table = []
    for i in range(len(table[0])):
        inverted_table.append(get_column(table, i))
    return inverted_table

def get_column(table, i):
    '''取得表格的第i列'''
    column = []
    for row in table:
        column.append(row[i])
    return column

if __name__ == '__main__':
    # just for testing

